package per.chenxin.tools.foundation;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Properties;
import java.util.Set;

public class DBUtil {

    public static final int INT_BEST_ROW_IDENTIFIER = 0;

    public static final int INT_GET_TABLES = 1;

    public static final String COLUMN_NAME = "COLUMN_NAME";

    public static long lastRunTime = 0;

    public static Connection getConnectionFromConnectionItem(ConnectionItem connectionItem)
        throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {

        Thread.currentThread().setContextClassLoader(Util.getURLClassLoader());
        Driver driver = (Driver) Class.forName(connectionItem.getClassName(), true,
            Util.getURLClassLoader()).newInstance();
        Properties properties = new Properties();
        properties.put(Constants.STR_DRIVER_PROPERTIES_USER, connectionItem.getUser());
        properties.put(Constants.STR_DRIVER_PROPERTIES_PASSWORD, connectionItem.getPassword());
        Connection connection = driver.connect(connectionItem.getURI(), properties);
        connection.setAutoCommit(connectionItem.isAutoCommit());
        return driver.connect(connectionItem.getURI(), properties);
    }

    public static MetaData[] getMetaDatas(ResultSetMetaData resultSetMetaData) throws SQLException {

        MetaData[] metaDatas = new MetaData[resultSetMetaData.getColumnCount()];
        for (int i = 0; i < metaDatas.length; i++) {
            metaDatas[i] = new MetaData(resultSetMetaData, i + 1);
        }

        return metaDatas;

    }

    public static int executeUpdate(Statement statement, String strSql) throws SQLException {

        return statement.executeUpdate(strSql);

    }

    public static int executeUpdate(Connection connection, String strSql) throws SQLException {

        Statement statement = null;
        try {
            statement = connection.createStatement();
            return executeUpdate(statement, strSql);
        } finally {
            DBUtil.closeStatement(statement);
        }

    }

    public static int getResultCount(Statement statement, String tableName) throws SQLException {

        ResultSet resultSet = null;
        try {
            resultSet = statement.executeQuery("select count('x') count from (" + tableName + ")");

            String strCount = getFirstRowString(resultSet, "count");
            resultSet.close();
            return Integer.parseInt(strCount);
        } finally {
            DBUtil.closeResultSet(resultSet);
        }
    }

    public static int getResultCount(Connection connection, String tableName) throws SQLException {

        Statement statement = null;
        try {
            statement = connection.createStatement();
            return getResultCount(statement, tableName);
        } finally {
            DBUtil.closeStatement(statement);
        }
    }

    public static void closeResultSet(ResultSet resultSet) {

        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
        }
        resultSet = null;
    }

    public static void closeStatement(Statement statement) {

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
            }
        }
        statement = null;
    }

    public static void closeConnection(Connection connection) {

        if (connection != null) {
            try {
                if (!connection.isClosed()) {
                    connection.rollback();
                    connection.close();
                }
            } catch (SQLException e) {
            }
        }
    }

    public static boolean isInstanceofDate(int columnType) {

        if (columnType == Types.DATE)
            return true;
        if (columnType == Types.TIMESTAMP)
            return true;
        if (columnType == Types.TIME)
            return true;
        return false;
    }

    public static boolean isInstanceofString(int columnType) {

        if (columnType == Types.CHAR)
            return true;
        if (columnType == Types.VARCHAR)
            return true;
        return false;
    }

    public static String toString(MetaData metaData, Object obj) throws SecurityException,
        IllegalArgumentException, ClassNotFoundException, NoSuchMethodException,
        IllegalAccessException, InvocationTargetException {

        return OracleStringConverter.getInstance().toJavaString(obj, metaData.columnType,
            metaData.columnClassName);

    }

    public static Object getValueFromResultSet(MetaData metaData, ResultSet resultSet,
        int columnIndex) throws SQLException {

        switch (metaData.columnType) {
            case Types.DATE:
                return resultSet.getDate(columnIndex);
            case Types.TIMESTAMP:
                return resultSet.getTimestamp(columnIndex);
            case Types.TIME:
                return resultSet.getTime(columnIndex);
            default:
                return resultSet.getObject(columnIndex);
        }

    }

    public static String createInsertSql(DBTable dbTable, String table) {

        //TODO:sort by order
        Set<String> keys = dbTable.getColumnItemKeySet(table);
        StringBuffer sb1 = new StringBuffer();
        StringBuffer sb2 = new StringBuffer();
        sb1.append("insert into ");
        sb1.append(table);
        sb1.append(" (");

        for (String column : keys) {
            sb1.append(column);
            sb1.append(",");
            sb2.append(OracleStringConverter.getInstance().toDbString(
                "",
                Integer.valueOf((String) dbTable.getColumnItem(table, column).get(
                    DBTable.STR_DATA_TYPE))));
            sb2.append(",");
        }

        sb1.deleteCharAt(sb1.length() - 1);
        sb2.deleteCharAt(sb2.length() - 1);
        sb1.append(" ) values ( ");
        sb1.append(sb2);
        sb1.append(" )");
        return sb1.toString();
    }

    public static String createInsertSql(String[] value, MetaData[] metaDatas, String table) {

        StringBuffer sb1 = new StringBuffer();
        StringBuffer sb2 = new StringBuffer();
        sb1.append("insert into ");
        sb1.append(table);
        sb1.append(" (");

        for (int i = 0; i < metaDatas.length; i++) {
            MetaData metaData = metaDatas[i];
            sb1.append(metaData.columnName);
            sb1.append(",");
            sb2.append(OracleStringConverter.getInstance().toDbString(value[i], metaData.columnType));
            sb2.append(",");
        }

        sb1.deleteCharAt(sb1.length() - 1);
        sb2.deleteCharAt(sb2.length() - 1);
        sb1.append(" ) values ( ");
        sb1.append(sb2);
        sb1.append(" )");
        return sb1.toString();
    }

    public static String createUpdateSql(DBTable dbTable, String table, String[] value) {

        StringBuffer sb1 = new StringBuffer();
        sb1.append("update ");
        sb1.append(table);
        sb1.append(" set ");

        Set<String> setKey = dbTable.getColumnImportedKeyKeySet(table);

        for (String strKey : setKey) {
            sb1.append(strKey);
            sb1.append("=");
            //sb2.append(OracleStringConverter.getInstance().toDbString(value[i], metaData.columnType));
            sb1.append(" ");
            sb1.append(",");
        }

        sb1.deleteCharAt(sb1.length() - 1);
        sb1.append(" ) values ( ");
        return sb1.toString();
    }

    public static ResultSet getDatabaseMetaDataInfo(DatabaseMetaData databaseMetaData, int info)
        throws SQLException, SecurityException, NoSuchMethodException, IllegalArgumentException,
        IllegalAccessException, InvocationTargetException {

        Method method;

        Object[] args;

        switch (info) {
            case INT_BEST_ROW_IDENTIFIER:
                method = databaseMetaData.getClass().getMethod("getBestRowIdentifier", new Class[] {
                    String.class, String.class, String.class, int.class, boolean.class
                });
                args = new Object[] {
                    null, null, null, DatabaseMetaData.bestRowTransaction, false
                };
                break;
            case INT_GET_TABLES:

                method = databaseMetaData.getClass().getMethod("getTables", new Class[] {
                    String.class, String.class, String.class, String[].class
                });
                args = new Object[] {
                    null, null, null, null
                };
                break;

            default:
                return null;
        }

        return (ResultSet) method.invoke(databaseMetaData, args);
    }

    public static String getFirstRowString(ResultSet resultSet, String column) throws SQLException {

        resultSet.next();
        return resultSet.getString(column);
    }

    public static String resultSetToString(ResultSet resultSet) throws SQLException {

        StringBuffer sb = new StringBuffer();

        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
            sb.append(resultSetMetaData.getColumnName(i + 1));
            sb.append("\t");
        }
        sb.append("\n");
        while (resultSet.next()) {
            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                sb.append(resultSet.getString(i + 1));
                sb.append("\t");

            }
            sb.append("\n");
        }

        return sb.toString();
    }
}
